﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.IO;
using Microsoft.Win32;
using System.Windows;

namespace KaoQin.Common
{
    class ExportFile
    {



        /// <summary>
        /// 将数据存储为Excel文件。
        /// </summary>
        /// <param name="p_dt">要保存的数据表。</param>
        public static void SaveAsExcelTableFile(object p_dt)
        {
            System.Data.DataTable dt = (System.Data.DataTable)p_dt;
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("没有可保存的数据");
                return;
            }
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "请选择文件存放路径";
            sfd.FileName = "导出数据";
            sfd.Filter = "Excel文档(*.xls)|*.xls";
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string FileName = sfd.FileName.ToLower();
            if (!FileName.Contains(".xls"))
            {
                FileName += ".xls";
            }
            if (FileName.Substring(FileName.LastIndexOf(Path.DirectorySeparatorChar)).Length <= 5)
            {
                MessageBox.Show("文件保存失败，文件名不能为空！");
                return;
            }
            if (sfd.FileName != "")
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                if (excelApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                    return;
                }
                else
                {
                    //MessageUtil.ShowThreadMessage("正在导出数据，请稍候...");
                    DateTime start = DateTime.Now;
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
                    Microsoft.Office.Interop.Excel.Range range = null;
                    object objMissing = System.Reflection.Missing.Value;

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = dt.TableName;
                    for (int col = 1; col <= dt.Columns.Count; col++)
                    {
                        worksheet.Cells[1, col] = dt.Columns[col - 1].Caption.ToString();

                    }
                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        for (int j = 1; j <= dt.Columns.Count; j++)
                        {
                            if (j != 2)
                            {
                                worksheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].ToString();

                            }
                            else
                            {
                                string rangeName = "B" + (i + 1);
                                range = worksheet.get_Range(rangeName, objMissing);
                                range.Select();
                                //range.ColumnWidth = 50;
                                range.RowHeight = 40;
                                //Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)worksheet.Pictures(objMissing);

                                float PicLeft, PicTop;
                                PicLeft = Convert.ToSingle(range.Left);
                                PicTop = Convert.ToSingle(range.Top);

                                //pics.Insert(FileHelper.GetcurrPath()+dt.Rows[i - 1][j - 1].ToString(), objMissing);
                                //参数含义：
                                //图片路径
                                //是否链接到文件
                                //图片插入时是否随文档一起保存
                                //图片在文档中的坐标位置（单位：points）
                                //图片显示的宽度和高度（单位：points）
                                //参数详细信息参见：
                                if (File.Exists(FileHelper.GetcurrPath() + dt.Rows[i - 1][1].ToString()))
                                {
                                    worksheet.Shapes.AddPicture(FileHelper.GetcurrPath() + dt.Rows[i - 1][1].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, 50, 40);
                                }


                            }

                        }
                    }
                    
                    //workbook.Sheets.Add(objMissing, objMissing, 1, XlSheetType.xlWorksheet);
                    

                    workbook.Saved = true;
                    workbook.SaveCopyAs(sfd.FileName);
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    workbook = null;
                    workbooks.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    workbooks = null;
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                    GC.Collect();
                    DateTime end = DateTime.Now;
                    int iTimeSapn = (end.Minute - start.Minute) * 60 + (end.Second - start.Second);
                    MessageBox.Show("数据导出完毕");
                }
            }
        }

        public static void SaveAsExcelTableFile2(object p_dt)
        {
            System.Data.DataTable dt = (System.Data.DataTable)p_dt;
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("没有可保存的数据");
                return;
            }
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "请选择文件存放路径";
            sfd.FileName = "导出数据";
            sfd.Filter = "Excel文档(*.xls)|*.xls";
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string FileName = sfd.FileName.ToLower();
            if (!FileName.Contains(".xls"))
            {
                FileName += ".xls";
            }
            if (FileName.Substring(FileName.LastIndexOf(Path.DirectorySeparatorChar)).Length <= 5)
            {
                MessageBox.Show("文件保存失败，文件名不能为空！");
                return;
            }
            if (sfd.FileName != "")
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                if (excelApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                    return;
                }
                else
                {
                    //MessageUtil.ShowThreadMessage("正在导出数据，请稍候...");
                    DateTime start = DateTime.Now;
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
                    Microsoft.Office.Interop.Excel.Range range = null;
                    object objMissing = System.Reflection.Missing.Value;

                    worksheet = (Worksheet)workbook.ActiveSheet;
                    worksheet.Name = dt.TableName;
                    for (int col = 1; col <= dt.Columns.Count; col++)
                    {
                        worksheet.Cells[1, col] = dt.Columns[col - 1].Caption.ToString();

                    }
                    for (int i = 1; i <= dt.Rows.Count; i++)
                    {
                        for (int j = 1; j <= dt.Columns.Count; j++)
                        {
                            if (j != 3 && j != 6 && j != 8 && j != 10 && j != 12 && j != 14 && j != 16)
                            {
                                worksheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].ToString();

                            }
                            else
                            {
                                string rangeName = GetColumn(j) + (i + 1);
                                range = worksheet.get_Range(rangeName, objMissing);
                                range.Select();
                                //range.ColumnWidth = 50;
                                range.RowHeight = 40;
                                //Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)worksheet.Pictures(objMissing);

                                float PicLeft, PicTop;
                                PicLeft = Convert.ToSingle(range.Left);
                                PicTop = Convert.ToSingle(range.Top);

                                //pics.Insert(FileHelper.GetcurrPath()+dt.Rows[i - 1][j - 1].ToString(), objMissing);
                                //参数含义：
                                //图片路径
                                //是否链接到文件
                                //图片插入时是否随文档一起保存
                                //图片在文档中的坐标位置（单位：points）
                                //图片显示的宽度和高度（单位：points）
                                //参数详细信息参见：
                                if (File.Exists(FileHelper.GetcurrPath() + dt.Rows[i - 1][j-1].ToString()))
                                {
                                    
                                    worksheet.Shapes.AddPicture(FileHelper.GetcurrPath() + dt.Rows[i - 1][j-1].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, 50, 40);
                                }


                            }

                        }
                    }

                    
                    workbook.Saved = true;
                    workbook.SaveCopyAs(sfd.FileName);
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    workbook = null;
                    workbooks.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    workbooks = null;
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                    GC.Collect();
                    DateTime end = DateTime.Now;
                    int iTimeSapn = (end.Minute - start.Minute) * 60 + (end.Second - start.Second);
                    MessageBox.Show("数据导出完毕");
                }
            }
        }

        public static string GetColumn(int colIndex)
        {
            switch (colIndex)
            { 
                case 3:
                    return "C";
                case 6:
                    return "F";
                case 8:
                    return "H";
                case 10:
                    return "J";
                case 12:
                    return "L";
                case 14:
                    return "N";
                case 16:
                    return "P";
                default:
                    return "";
                    

            }
        }
        /// <summary>
        /// 将数据集存储为Excel文件。
        /// </summary>
        /// <param name="p_ds">要导出的数据集。</param>
        public static void SaveAsExcelSetFile(object p_ds)
        {
            System.Data.DataSet ds = (System.Data.DataSet)p_ds;
            if (ds == null || ds.Tables.Count == 0)
            {
                MessageBox.Show("没有可保存的数据");
                return;
            }
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Title = "请选择文件存放路径";
            sfd.FileName = "导出数据";
            sfd.Filter = "Excel文档(*.xls)|*.xls";
            if (sfd.ShowDialog() != true)
            {
                return;
            }
            string FileName = sfd.FileName.ToLower();
            if (!FileName.Contains(".xls"))
            {
                FileName += ".xls";
            }
            if (FileName.Substring(FileName.LastIndexOf(Path.DirectorySeparatorChar)).Length <= 5)
            {
                MessageBox.Show("文件保存失败，文件名不能为空！");
                return;
            }
            if (sfd.FileName != "")
            {
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                if (excelApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                    return;
                }
                else
                {
                    //MessageUtil.ShowThreadMessage("正在导出数据，请稍候...");
                    DateTime start = DateTime.Now;
                    Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
                    Microsoft.Office.Interop.Excel.Range range = null;
                    object objMissing = System.Reflection.Missing.Value;
                    for (int m = 0; m < ds.Tables.Count; m++)
                    {
                        System.Data.DataTable dt = ds.Tables[m];
                        worksheet = (Worksheet)workbook.ActiveSheet;
                        worksheet.Name = dt.TableName;
                        for (int col = 1; col <= dt.Columns.Count; col++)
                        {
                            worksheet.Cells[1, col] = dt.Columns[col - 1].Caption.ToString();

                        }
                        for (int i = 1; i <= dt.Rows.Count; i++)
                        {
                            for (int j = 1; j <= dt.Columns.Count; j++)
                            {
                                if (j != 2)
                                {
                                    worksheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].ToString();

                                }
                                else
                                {
                                    string rangeName = "B" + (i + 1);
                                    range = worksheet.get_Range(rangeName, objMissing);
                                    range.Select();
                                    //range.ColumnWidth = 50;
                                    range.RowHeight = 40;
                                    //Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)worksheet.Pictures(objMissing);

                                    float PicLeft, PicTop;
                                    PicLeft = Convert.ToSingle(range.Left);
                                    PicTop = Convert.ToSingle(range.Top);

                                    //pics.Insert(FileHelper.GetcurrPath()+dt.Rows[i - 1][j - 1].ToString(), objMissing);
                                    //参数含义：
                                    //图片路径
                                    //是否链接到文件
                                    //图片插入时是否随文档一起保存
                                    //图片在文档中的坐标位置（单位：points）
                                    //图片显示的宽度和高度（单位：points）
                                    //参数详细信息参见：
                                    if (File.Exists(FileHelper.GetcurrPath() + dt.Rows[i - 1][1].ToString()))
                                    {
                                        worksheet.Shapes.AddPicture(FileHelper.GetcurrPath() + dt.Rows[i - 1][1].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, PicLeft, PicTop, 50, 40);
                                    }


                                }

                            }
                        }
                        if (m < ds.Tables.Count - 1)
                        {
                            workbook.Sheets.Add(objMissing, objMissing, 1, XlSheetType.xlWorksheet);
                        }
                    }
                    workbook.Saved = true;
                    workbook.SaveCopyAs(sfd.FileName);
                    //释放资源
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                    worksheet = null;
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    workbook = null;
                    workbooks.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    workbooks = null;
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                    GC.Collect();
                    DateTime end = DateTime.Now;
                    int iTimeSapn = (end.Minute - start.Minute) * 60 + (end.Second - start.Second);
                    MessageBox.Show("数据导出完毕");
                }
            }
        }


    }
}
